﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Stanford.Data;

namespace Stanford.Demo.Demo_ADO.Net
{
    public partial class ADO_Net : System.Web.UI.Page
    {
        public string Html { get; set; }
        public List<Student> LstStudent = new List<Student>();
        private SqlConnection ConnectionString = null;

        protected void Page_Load(object sender, EventArgs e)
        {
            DemoSqlClient();
        }
        public void DemoSqlClient()
        {
            try
            {
                var connStr = GetConnectionString();
                string qString = "Select * from Student"
                                    + " WHERE St_Age > @St_Age "
                                    + " ORDER BY St_Age DESC;";
                int paramValue = 20;
                using (ConnectionString = new SqlConnection(connStr))
                {
                    // tạo đối tượng command và param
                    var command = new SqlCommand(qString, ConnectionString);
                    command.Parameters.AddWithValue("@St_Age", paramValue);
                    // execute  DataReader và in kế quả
                    ConnectionString.Open();
                    #region ExecuteReader
                    SqlDataReader reader = command.ExecuteReader();
                    Html = "<table border='1'><tr>";
                    while (reader.Read())
                    {
                        Html += string.Format("<td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td>", reader[1], reader[2], reader[3], reader[4], reader[5]) + "</tr><tr>";
                    }
                    Html += "</tr></table>";
                    reader.Close();
                    #endregion

                    #region DataSet
                    var dadapter = new SqlDataAdapter();
                    dadapter.SelectCommand = command;
                    var dset = new DataSet();
                    dadapter.Fill(dset);

                    grvData.DataSource = dset;
                    grvData.DataBind();
                    #endregion DataSet

                    #region

                    #endregion

                }
            }
            catch (Exception ex)
            {
                Html = ex.Message;
            }
            finally
            {
                // dispose of open objects
                if (ConnectionString != null)
                    ConnectionString.Close();
            }
        }
        /// <summary>
        /// Lấy chuỗi kết nối
        /// </summary>
        /// <returns></returns>
        public string GetConnectionString()
        {
            var connStr = "Data Source=(local);Initial Catalog=StanfordDatabase;Persist Security Info=True;User ID=sa;Password=1234$;MultipleActiveResultSets=True";
            return connStr;
        }
        /// <summary>
        /// Đăng nhập
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnLogin_Click(object sender, EventArgs e)
        {
            try
            {
                var connStr = GetConnectionString();

                using (ConnectionString = new SqlConnection(connStr))
                {
                    string userName = txtUserName.Text;
                    string pass = txtPass.Text;
                    string qString = "Select Count(*) from Student"
                                   + " WHERE St_Name = N'" + userName + "' and Pass_Word ='" + pass + "';";
                    var command = new SqlCommand(qString, ConnectionString);
                    ConnectionString.Open();
                    #region ExecuteReader
                    int rerult = (int)command.ExecuteScalar();
                    if (rerult > 0)
                    {
                        lbThongBao.Text = "<br/>Đăng nhập thành công! Xin chào bạn " + userName;
                    }
                    else
                    {
                        lbThongBao.Text = "<br/> (*) Tên đăng nhập hoặc mật khẩu không đúng";
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                Html = ex.Message;
            }
            finally
            {
                // dispose of open objects
                if (ConnectionString != null)
                    ConnectionString.Close();
            }

        }


    }
}